﻿CREATE TABLE [Projects].[datProjects] (
    [ProjectID]           INT             IDENTITY (1, 1) NOT NULL,
    [Intern]              BIT             CONSTRAINT [DF_datProjects_Intern] DEFAULT ((0)) NOT NULL,
    [ProjectStatus]       TINYINT         NOT NULL,
    [MatchCode]           NVARCHAR (100)  NOT NULL,
    [ProjectNumber]       SMALLINT        NULL,
    [PLZ]                 NCHAR (10)      NOT NULL,
    [StandortID]          INT             NULL,
    [CustomerSource]      INT             NOT NULL,
    [FirstContactDate]    DATETIME2 (7)   NULL,
    [ImplementationDate]  DATETIME2 (7)   NULL,
    [OfferDate]           DATETIME2 (7)   NULL,
    [OrderDate]           DATETIME2 (7)   NULL,
    [FinishDateProjected] DATETIME2 (7)   NULL,
    [FinishDateReal]      DATETIME2 (7)   NULL,
    [CreateOn]            DATETIME2 (7)   NOT NULL,
    [CreateBy]            INT             NOT NULL,
    [LastChangedOn]       DATETIME2 (7)   NOT NULL,
    [LastChangedBy]       INT             NOT NULL,
    [FilePath]            NVARCHAR (255)  NOT NULL,
    [CalculationFileName] NVARCHAR (100)  NOT NULL,
    [Distance]            DECIMAL (11, 2) CONSTRAINT [DF_datProjects_Distance] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_datProjects] PRIMARY KEY CLUSTERED ([ProjectID] ASC),
    CONSTRAINT [FK_datProjects_domProjectStatus] FOREIGN KEY ([ProjectStatus]) REFERENCES [Projects].[domProjectStatus] ([ProjectStatus])
);








GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Projects_datProjects_ShortName]
    ON [Projects].[datProjects]([MatchCode] ASC);



